/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package gruppe7.data;

import java.sql.*;
import gruppe7.db.DBAccess;
import gruppe7.tlrs.Station;
import java.util.ArrayList;

/**
 *
 * @author bertram
 */
public class DBStationProvider implements StationProvider {

    public DBStationProvider() {
    }

    public ArrayList<String> getStationList() {
        ArrayList<String> stationList = new ArrayList<String>();
        try {
            Connection conn = DBAccess.getInstance().getConnection();
            String sql = "SELECT name, zone FROM station";

            PreparedStatement stmt = conn.prepareStatement(sql);
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                String s = rs.getString("name");
                stationList.add(s);
            }

            rs.close();
            conn.close();



        } catch (Exception e) {
            e.printStackTrace();
        }

        return stationList;

    }

    public String getManager(int stationID) {
        String manager = "";
        try {
            Connection conn = DBAccess.getInstance().getConnection();
            String sql = "SELECT * FROM manager WHERE stationID = ?";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(1, stationID);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                do {
                    manager = rs.getString("name");
                } while (rs.next());
            } else {
                manager = "unmanned";
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return manager;
    }

    public Station getStation(String name) {
        Station s = null;
        try {
            Connection conn = DBAccess.getInstance().getConnection();
            String sql = "SELECT * from station where name = ?";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setString(1, name);
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                s = new Station(rs.getString("name"), rs.getInt("zone"), rs.getInt("stationID"));
                s.setErectionDate(rs.getString("erectiondate").toString());
                s.setComment(rs.getString("comment"));
                s.setManager(getManager(s.getStationID()));
            }

            rs.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return s;
    }

    public int getPrice(String departure, String arrival) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public void createStation() {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public Station updateStationInfo(Station station) {
        int currentStation = station.getStationID();


        try {

            Connection conn = DBAccess.getInstance().getConnection();
            String sql = "UPDATE station set name = ?, zone = ?, comment = ?,"
                    + "erectiondate = ? WHERE stationID = ?";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setString(1, station.getName());
            stmt.setInt(2, station.getZone());
            stmt.setString(3, station.getComment());
            stmt.setString(4, station.getErectionDate());
            stmt.setInt(5, station.getStationID());
            stmt.execute();
            Station updatedStation = getStationById(station.getStationID());
            return updatedStation;
        } catch (Exception e) {
            e.printStackTrace();
            return station;
        }
    }

    public Station getStationById(int stationID) {
        Station s = null;
        try {
            Connection conn = DBAccess.getInstance().getConnection();
            String sql = "SELECT * from station where stationID = ?";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(1, stationID);
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                s = new Station(rs.getString("name"), rs.getInt("zone"), rs.getInt("stationID"));
                s.setErectionDate(rs.getString("erectiondate"));
            }

            rs.close();
            conn.close();
        } catch (Exception e) {
        }

        return s;
    }
}
